﻿CREATE PROCEDURE [RestartFramework].[PutETLJobStage]
		@pETLJobId				INT
,		@pETLJobStageName		SYSNAME
,		@pETLJobStageOrder		SMALLINT    =   NULL
,		@pETLJobStageEnabled	BIT			=	1
,		@pUpdateIfExists		BIT			=	1	/*Very plausible that we don't want to update an existing record thus let's allow that to be specified*/
,		@pETLJobStageId			SMALLINT	=   NULL			OUTPUT
,       @pPrint                 BIT         =   1
AS
BEGIN	
		SET NOCOUNT ON;
		BEGIN TRY
				DECLARE  @vRowCount  INT = 0;

				/*If no ETLJobStageOrder defined, use existing*/
				SELECT  @pETLJobStageOrder = COALESCE(@pETLJobStageOrder,tgt.[ETLJobStageOrder])
				FROM    [RestartFramework].[ETLJobStage] tgt
				WHERE   tgt.[ETLJobId]          =   @pETLJobId
					AND tgt.[ETLJobStageName]   =   @pETLJobStageName;
				IF  (@pETLJobStageOrder IS NULL)
				BEGIN
						RAISERROR('You must provide a Job Stage Order when creating new JobStage=%s',16,1,@pETLJobStageName);
				END

				MERGE	[RestartFramework].[ETLJobStage] tgt
				USING	(
						SELECT	[ETLJobStageName]		=	@pETLJobStageName
						,		[ETLJobId]				=	@pETLJobId
						,		[Order]					=	@pETLJobStageOrder
						,		[ETLJobStageEnabled]	=	@pETLJobStageEnabled
						) src
					ON	src.[ETLJobId]			=	tgt.[ETLJobId]
					AND	src.[ETLJobStageName]	=	tgt.[ETLJobStageName]
				WHEN NOT MATCHED THEN
						INSERT	([ETLJobStageName],[ETLJobId],[ETLJobStageOrder],[ETLJobStageEnabled])		
						VALUES	(src.[ETLJobStageName],src.[ETLJobId],src.[Order],src.[ETLJobStageEnabled])
				WHEN MATCHED AND	(	src.[Order]					<>	tgt.[ETLJobStageOrder]
									OR	src.[ETLJobStageEnabled]	<>	tgt.[ETLJobStageEnabled]
									)
							AND		@pUpdateIfExists = 1
							THEN
						UPDATE
						SET		[ETLJobStageOrder]		=	src.[Order]
						,		[ETLJobStageEnabled]	=	src.[ETLJobStageEnabled]
				;
				SET @vRowCount = @@ROWCOUNT;

				SET	@pETLJobStageId	=	(
											SELECT	ejs.[ETLJobStageId]
											FROM	[RestartFramework].[ETLJobStage] ejs
											WHERE	ejs.[ETLJobId]			=	@pETLJobId											
												AND	ejs.[ETLJobStageName]	=	@pETLJobStageName
										);
				IF(@pPrint = 1)
						PRINT   CONVERT(NVARCHAR(5),@vRowCount) + N' rows inserted/updated in [RestartFramework].[ETLJobStage] for ETLJobStageName=' + 
										@pETLJobStageName + N', ETLJobStageID=' + CONVERT(NVARCHAR(10),@pETLJobStageID);
		END TRY
		BEGIN CATCH
				SET	@pETLJobStageId = NULL;
				THROW;
		END CATCH
END